/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Xpath;

import DTO.ChuyenMuc;
import DTO.TinTuc;
import DTO.Trang;
import Util.DataProvider;
import DTO.Xpath;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.table.DefaultTableModel;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;

/**
 *
 * @author THIEUQUANG
 */
public class Function {

    
    public static ArrayList<TinTuc> GetNewFormDatabase(String weblink) throws ClassNotFoundException, SQLException {
        ArrayList<TinTuc> al = new ArrayList<TinTuc>();

        DataProvider dp = new DataProvider();

        dp.open();

        ResultSet rs = dp.CallStore_spLayTinTuc(weblink);

        while (rs.next()) {
            TinTuc tt = new TinTuc();

            tt.setMaTinTuc(rs.getInt("MaTinTuc"));
            tt.setLink(rs.getString("Link"));
            tt.setTitle(rs.getNString("Title"));
            tt.setImage(rs.getString("Image"));
            tt.setTime(rs.getString("Time"));
            tt.setDescrible(rs.getNString("Describle"));

            al.add(tt);
        }

        dp.close();

        return al;
    }

    /**
     * Lay DateNews
     */
    public static ArrayList<String> GetDateNews(Xpath xpath) throws Exception {
        ArrayList<String> link = new ArrayList<String>();
        ArrayList<String> node = new ArrayList<String>();
        ArrayList<String> date = new ArrayList<String>();
        link = GetList(xpath.getLink());
        for (int i = 0; i < link.size(); i++) {
            //chá»‰ láº¥y tin thuá»™c chuyen muc dang xÃ©t

            Convert2Xml(link.get(i));
            node = GetList(xpath.getTime());

            date.add(node.get(0));
            System.out.println("\nDate:  " + node.get(0));

            node.clear();
        }

        return date;
    }

    /**
     * Lưu tin tức vào CSDL
     *
     * @param xl
     * @throws Exception
     */
    public static void NewsToDatabase(Xpath xl, String WebLink) throws Exception {
        DataProvider dp = new DataProvider();
        dp.open();
        //luu mot tin vao CSDL
        ArrayList<ArrayList<String>> al = new ArrayList<ArrayList<String>>();
        al = GetContent(xl);

        int length = al.get(0).size();
        String s1, s2, s3, s4, s5;

        for (int i = 0; i < length; i++) {
            //Lấy thông tin trong các node            
            s1 = al.get(0).get(i);
            s2 = al.get(1).get(i);
            s3 = al.get(2).get(i);
            s4 = al.get(3).get(i);
            s5 = al.get(4).get(i);
            
            //Lay href tin moi nhat truoc do
            
            //Gọi Store lưu trữ
            //System.out.println("=======\n" + s1 + "\n" + s2 + "\n" + s3 + "\n" + s4 + "\n" + s5 + "======");
            dp.CallStore_spThemTinMoi(WebLink, s1, s2, s3, s4, s5);

        }
        dp.close();
    }

    /**
     * Lưu xpath vào CSDL
     *
     * @param xl
     */
    public static void XpathToDatabase(Xpath xl) {
        //luu noi dung xpath cua trang vao CSDL

    }

    /**
     * Chuyển HTML thành XML
     *
     * @param url
     * @throws Exception
     */
    public static void Convert2Xml(String url) throws Exception {
        xpath.HTMLtoXML.HTML2XML(url);
    }

    /**
     * Lấy thông từ từ các chuỗi xpath
     *
     * @param expression
     * @return
     * @throws Exception
     */
    public static ArrayList<String> GetList(String expression) throws Exception {
        XPathFactory factory = XPathFactory.newInstance();
        XPath xPath = factory.newXPath();

        ArrayList<String> list = new ArrayList<String>();
        NodeList nodeList;
         File directory = new File("");
         String DuongDan = directory.getAbsolutePath();
        nodeList = (NodeList) xPath.evaluate(expression, new InputSource(new FileReader(DuongDan+"\\temp.xml")), XPathConstants.NODESET);
        for (int i = 0; i < nodeList.getLength(); i++) {
            String str = nodeList.item(i).getTextContent().trim();
            //fix lỗi box_comment
            if (str.toUpperCase().indexOf("#box_comment".toUpperCase()) < 0) {
                if (str.length() > 1) {
                    list.add(str);
                }
            }

        }
        return list;
    }

    /**
     * Lấy thông tin từ xpath đã cung cấp
     *
     * @param xl
     * @return
     * @throws Exception
     */
    public static ArrayList<ArrayList<String>> GetContent(Xpath xl) throws Exception {
        ArrayList<ArrayList<String>> arrayList = new ArrayList<ArrayList<String>>();

        ArrayList<String> nl1 = GetList(xl.getLink());
        arrayList.add(nl1);

        ArrayList<String> nl2 = GetList(xl.getTitle());
        arrayList.add(nl2);

        ArrayList<String> nl3 = GetList(xl.getImage());
        arrayList.add(nl3);

        ArrayList<String> nl5 = GetList(xl.getDescrible());

        //vnexpress: lay dc dateNews inside
        // ArrayList<String> nl4 = GetDateNews(xl);//GetList(xl.getTime());
        //tuoitre: chi lay dc date outside
        //ArrayList<String> nl4 = GetList(xl.getTime());
        ArrayList<String> nl4 = GetList(xl.getTime());
        arrayList.add(nl4);
        arrayList.add(nl5);

        return arrayList;
    }

    /**
     * Trả ra tên trang web từ weblink
     *
     * @param url
     * @return
     */
    public static String GetNameOfWeb(String url) {
        String name = null;

        //Lay ten trang
        String[] temp = url.split("/");
        name = temp[2];

        return name;
    }

    /**
     * Kiểm tra trang tồn tại
     *
     * @param url
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean CheckNameOfWeb(String url) throws ClassNotFoundException, SQLException {
        String name = GetNameOfWeb(url);
        //Kiểm tra trong CSDL
        //nếu không có tên trang web return false
        String sql = String.format("Select * from Trang where TenTrang='%s'", name);
        DataProvider dp = new DataProvider();
        dp.open();

        ResultSet rs = dp.ExecuteQuery(sql);
        if (!rs.next()) {
            return false;
        }

        dp.close();

        return true;
    }

    public static boolean CheckWebLink(String weblink) throws ClassNotFoundException, SQLException {
        String sql = String.format("Select * from chuyenmuc where weblink='%s'", weblink);
        DataProvider dp = new DataProvider();
        dp.open();

        ResultSet rs = dp.ExecuteQuery(sql);
        if (!rs.next()) {
            return false;
        }

        dp.close();
        return true;
    }

    public static void AddPage(ChuyenMuc cm, Xpath xpath) throws SQLException, ClassNotFoundException, Exception {
        //Kiểm tra trang tồn tại
        String name = GetNameOfWeb(cm.getWebLink());
        if (CheckNameOfWeb(cm.getWebLink()) == false) {
            //gọi store thêm trang mới, chuyên mục mới và xpath mới
            DataProvider dp = new DataProvider();
            dp.open();

            dp.CallStore_spThemTrangMoi(name, cm, xpath);

            dp.close();
        } else if (CheckWebLink(cm.getWebLink()) == false) {//tồn tại tên trang nhưng ko có weblink này

            DataProvider dp = new DataProvider();
            dp.open();

            dp.CallStore_spThemWebLink(name, cm);

            dp.close();
        }

        //Convert trang sang XML
        Convert2Xml(cm.getWebLink());

        //Lấy tin tức lưu vào CSDL
        NewsToDatabase(xpath, cm.getWebLink());

        //NextPage(cm.getWebLink(), xpath);
    }

    public static DefaultMutableTreeNode MakeTree() throws ClassNotFoundException, SQLException {
        String sql = "Select * from trang";
        DefaultMutableTreeNode root = new DefaultMutableTreeNode("All");

        DataProvider dp = new DataProvider();
        dp.open();

        ResultSet rs = dp.ExecuteQuery(sql);
        while (rs.next()) {
            Trang t = new Trang();

            t.setMaTrang(rs.getInt("MaTrang"));
            t.setTenTrang(rs.getString("TenTrang"));

            DefaultMutableTreeNode node = new DefaultMutableTreeNode(t.getTenTrang());
            DataProvider dp1 = new DataProvider();

            dp1.open();

            sql = "select * from chuyenmuc where matrang=" + t.getMaTrang();

            ResultSet rs1 = dp.ExecuteQuery(sql);

            while (rs1.next()) {
                ChuyenMuc cm = new ChuyenMuc();
                cm.setTenCM(rs1.getNString("TenCM"));
                DefaultMutableTreeNode sub = new DefaultMutableTreeNode(cm.getTenCM());
                node.add(sub);
            }

            dp1.close();

            root.add(node);
        }
        dp.close();
        return root;
    }

    public static DefaultTableModel MakeTable() throws SQLException, ClassNotFoundException {
        DefaultTableModel model = new DefaultTableModel();
        ArrayList<ChuyenMuc> al = new ArrayList<ChuyenMuc>();

        DataProvider dp = new DataProvider();
        dp.open();

        ResultSet rs = dp.ExecuteQuery("Select * from chuyenmuc");
        while (rs.next()) {
            ChuyenMuc chuyenMuc = new ChuyenMuc();

            chuyenMuc.setMaCM(rs.getInt("MaCM"));
            chuyenMuc.setTenCM(rs.getNString("TenCM"));
            chuyenMuc.setWebLink(rs.getString("WebLink"));
            chuyenMuc.setMaTrang(rs.getInt("MaTrang"));

            al.add(chuyenMuc);
        }
        dp.close();
        //tao cot
        model.addColumn("Mã chuyên mục");
        model.addColumn("Tên chuyên mục");
        model.addColumn("Weblink");
        model.addColumn("Mã trang");
        //tao so luong dong
        model.setRowCount(al.size());

        for (int i = 0; i < al.size(); i++) {
            //them du lieu vao cac dong
            ChuyenMuc cm = al.get(i);
            model.setValueAt(cm.getMaCM(), i, 0);
            model.setValueAt(cm.getTenCM(), i, 1);
            model.setValueAt(cm.getWebLink(), i, 2);
            model.setValueAt(cm.getMaTrang(), i, 3);
        }
        return model;
    }

    public static int DeleteCategory(String WebLink) throws ClassNotFoundException, SQLException {
        DataProvider dp = new DataProvider();
        dp.open();

        String sql = String.format("delete from chuyenmuc where weblink='%s'", WebLink);

        int kq = dp.executeUpdate(sql);

        dp.close();
        return kq;
    }

    public static void GetNewsAgain(String weblink) throws ClassNotFoundException, SQLException, Exception {

        Xpath x = new Xpath();

        String sql = String.format("select x.* from chuyenmuc cm, xpath x where cm.weblink='%s' and cm.macm=x.macm", weblink);
        DataProvider dp = new DataProvider();
        dp.open();

        ResultSet rs = dp.ExecuteQuery(sql);

        rs.next();

        x.setLink(rs.getString("XpathLink"));
        x.setTitle(rs.getString("XpathTitle"));
        x.setImage(rs.getString("XpathImg"));
        x.setDescrible(rs.getString("XpathDesc"));
        x.setTime(rs.getString("XpathTime"));

        dp.close();

        //Convert trang sang XML
        Convert2Xml(weblink);

        //Lấy tin tức lưu vào CSDL
        NewsToDatabase(x, weblink);
    }

    public static void NextPage(String weblink, Xpath x) throws Exception {
        String name = GetNameOfWeb(weblink);

        Convert2Xml("http://" + name + GetName(x.getNext()));

        NewsToDatabase(x, weblink);
    }
    
    public static String GetName(String str) throws XPathExpressionException, FileNotFoundException
    {
        XPathFactory factory = XPathFactory.newInstance();
        XPath xPath = factory.newXPath();

        String result = xPath.evaluate(str, new InputSource(new FileReader("temp.xml")));
        
        return result;
    }
}
